const express = require('express')
const router = express.Router()
const mysql = require('mysql')
const path = require('path')
const fs = require('fs')
const { status200, status500 } = require('../status')
const { createId } = require('../util/index')
const { filterQuery, pinjieSql, filterArrs, pinjieMore, pinjieLike, pinjieEdit } = require('../util/tool')
const multer = require('multer')
const {env}=require('../dataBase')
// const env = {
//     host: "111.231.22.97",
//     user: "cqbase",
//     password: "MZM5j38RNCjdPRP7",
//     database: "cqbase", //数据库名字
//     multipleStatements: true //允许多条sql语句查询
// }

//暴露地址
const uploadURL = path.join(__dirname, '/file')
router.use(express.static(uploadURL))



//获取列表
var pageSize = 9 //每次加载9条
const getInfo = async (req, res) => {
    let arr = await filterQuery(req.query)
    let pool = mysql.createPool(env)
    let pageNo = Number(req.query['pageNo'])
    let offset = (pageNo - 1) * pageSize;
    //全部查询
    if (arr.length == 0) {
        console.log('全部查询')
        let sql = 'select * from infolist ORDER BY date DESC limit ?,?;select COUNT(*) from infolist'
        pool.query(sql, [offset, pageSize], (err, result) => {
            try {
                status200(res, { msg: 'success', data: { data: result[0], total: result[1][0]['COUNT(*)'] } })
            } catch {
                res.status(500)
                console.log(err)
            }
        })
    }
    //子查询
    else {
        let sqlarr = await pinjieLike(arr, 'select * from infolist where', 'select COUNT(*) from infolist where', req)
        let sql = sqlarr[0] + ';' + sqlarr[1]
        let arrs = await filterArrs(arr, req.query)
        arrs = [...[offset, pageSize], ...await filterArrs(arr, req.query)]
        pool.query(sql, arrs, (err, result) => {
            try {
                status200(res, { msg: 'success', data: { data: result[0], total: result[1][0]['COUNT(*)'] } })
            } catch {
                res.status(500)
                console.log(err)
            }
        })
    }
}
router.get('/getInfo', (req, res) => {
    getInfo(req, res)
})






//查询详情
router.get('/getInfoDetail', async (req, res) => {
    let pool = mysql.createPool(env)
    let { id } = req.query
    let nameList = ['infolist', 'infodetail']
    let sql = await pinjieMore(nameList)
    pool.query(sql, [id], (err, result) => {
        try {
            let readURL = path.join(__dirname, `/file/${result[0].url}`)
            fs.readFile(readURL, 'utf-8', (err, data) => {
                try {
                    res.send({
                        code: 0,
                        msg: 'success',
                        data: {
                            id: result[0].id,
                            html: data,
                            data: result[0]
                        }
                    })
                }
                catch {
                    console.log(err)
                }
            })
        } catch {
            res.status(500)
            console.log(err)
        }
    })
})


//图片保存地址
const storage = multer.diskStorage({
    destination: (req, file, cb) => {
        cb(null, uploadURL);
    },
    filename: (req, file, cb) => {
        file.originalname = Buffer.from(file.originalname, "latin1").toString(
            "utf-8"
        );
        console.log(file.originalname)
        cb(null, file.originalname);
    },
});
const upload = multer({ storage: storage });
//上传文件
router.post('/upload', upload.single('file'), (req, res) => {
    if (req.file) {
        res.send({
            errno: 0,
            msg: 'success',
            data: {
                url: req.file.originalname
            }
        })
    }
})


const fsURL = path.join(__dirname, '/file/')

//写入数据
const writeFile = (html, url) => {
    return new Promise((reslove, reject) => {
        let urls = path.join(__dirname, `/file/${url}`)
        console.log(urls)
        fs.writeFile(urls, html, (err) => {
            if (err) {
                reslove(false)
            }
            else {
                reslove(true)
            }
        })
    })
}
//新增数据 infolist 和 infodetail
const addInfo = (arr) => {
    return new Promise((resolve, reject) => {
        let sql = 'insert into infolist values(?,?,?,?,?);insert into infodetail values(?,?)'
        let pool = mysql.createPool(env)
        pool.query(sql, arr, (err, result) => {
            try {
                resolve(true)
            } catch {
                resolve(false)
            }
        })
    })
}


const postInfo = async (req, res) => {
    console.log('ok 了')
    let id = createId()
    //写入数据
    let fileurl = + id + '.txt'
    let { html, cover, title, date, author } = req.body
    let arr = [id, date, author, title, cover, id, fileurl]
    let wf = await writeFile(html, fileurl)
    let aI = await addInfo(arr)
    if (!wf) {
        status500(res, { msg: '写入失败' })
        return
    }
    if (!aI) {
        status500(res, { msg: '存入失败' })
        return
    }
    status200(res, { msg: 'success' })
}
router.post('/postInfo', (req, res) => {
    postInfo(req, res)
})

//删除
const deleteInfo = async (req, res) => {
    // console.log(req.body['id'])
    let sql = 'delete from infolist where id=?;delete from infodetail where id = ?'
    let pool = mysql.createPool(env)
    let queryArr = [req.body['id'], req.body['id']]
    pool.query(sql, queryArr, (err, result) => {
        try {
            //后继改进
            status200(res, { msg: '删除成功' })
        }
        catch {
            res.status(500)
            console.log(err)
        }
    })
}
router.post('/deleteInfo', (req, res) => {
    deleteInfo(req, res)
})


//编辑



const updateInfoDetail = async(req, res) => {
    console.log(req.body['id'], 'id')
    let url = path.join(__dirname, `/file/${req.body['id']}.txt`)
    fs.writeFile(url, req.body['html'], 'utf-8', async(err) => {
        try {
            //写入成功
            let sqls =await pinjieEdit('update infolist ',['cover','date','title'])
            let {cover,date,title,id}=req.body
            let pool=mysql.createPool(env)
            let arr=[cover,date,title,id]
            console.log(arr,'arr')
            console.log(sqls,'sqls')
            pool.query(sqls,arr,(err,result)=>{
                try{
                 console.log(result)
                 if(result.affectedRows>=0){
                    status200(res,{msg:'编辑成功'})
                 }
                 else{
                    status500(res,{msg:'编辑失败'})
                 }
                }catch{
                    res.status(500)
                    console.log(err)
                }
            })
        } catch {
            res.status(500)
            console.log(err)
        }
    })
    //首先
}
router.post('/updateInfoDetail', (req, res) => {
    updateInfoDetail(req, res)
})





module.exports = router